<?php
header('Content-Type:text/html;charset=utf-8');
class SQLClass
{

	public $servername = "localhost";
	public $username = "root";
	public $password = "123456";
	public $dbname = "stockbacktest2";
	public $conn;
	public function __construct()
	{
		$this->conn = new mysqli($this->servername, $this->username, $this->password, $this->dbname);
		if ($this->conn->connect_error) {
			die("连接失败: " . $this->conn->connect_error);
		}
	}


	/**********************************************************************
	 * 获取指定日期的所有满足条件的 ： 股票代码，开盘价格，开盘时间
	 * @param $Day 指定日期
	 * @return 股票代码，开盘价格，开盘时间
	 *********************************************************************/
	function selectCode($Day,$orderRule)
	{
		// order by 默认 asc     desc 需要声明
		
			$sql = " SELECT *
			FROM  temp_daily2 
			WHERE trade_date = '" . $Day . "'
			ORDER BY ".$orderRule.";";
		
		//var_dump($sql);
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row);
		}
		$result->free();
		return $result_arr;
	}


	/********************************************************************
	 * 根据$股票代码,$卖出时间 获取卖出价
	 * @param $Day 指定日期
	 * @return $卖出时间 获取卖出价
	 ********************************************************************/
	function selectSellInfor($TsCode, $SellTime)
	{
		$sql =
			"SELECT trade_date,`close`
			FROM daily_table2
			WHERE ts_code = '" . $TsCode .
			"' AND trade_date = '" . $SellTime . "';";

		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row);
		}
		$result->free();
		return $result_arr;
	}


	/********************************************************************
	 * 根据$股票代码,$买入时间 获取买入时间，股票信息
	 * @param $Day 指定日期
	 * @return $卖出时间 获取卖出价
	 ********************************************************************/
	function select_Buyinfor($TsCode, $BuyTime)
	{

		$sql =
			"SELECT daily_table2.ts_code,`open`,trade_date,`name`,close
			FROM daily_table2
			WHERE daily_table2.ts_code = '" . $TsCode . "' 
			AND trade_date = '" . $BuyTime . "';";

			// 未优化前   
			// "SELECT daily_table2.ts_code,`open`,trade_date,`name`,close
			// FROM daily_table2,astock_base
			// WHERE daily_table2.ts_code = '" . $TsCode . "' 
			// AND daily_table2.ts_code = astock_base.ts_code
   		    // AND trade_date = '" . $BuyTime . "';";
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = null;
		while ($row = $result->fetch_assoc()) {
			$result_arr = $row;
		}
		$result->free();
		return $result_arr;
	}
/********************************************************************
	 * 根据$股票代码,$买入时间 获取买入时间，股票信息
	 * @param $Day 指定日期
	 * @return $卖出时间 获取卖出价（相对于上面的函数，获得多个字段信息）
	 ********************************************************************/
	function select_Buyinfor2($TsCode, $BuyTime){
		$sql =
			"SELECT trade_date,open,high,low,close,vol
			FROM daily_table2
			WHERE daily_table2.ts_code = '" . $TsCode . "'
			AND trade_date = '" . $BuyTime . "';";	
            // "SELECT trade_date,open,high,low,close,vol
			// FROM daily_table2,astock_base
			// WHERE daily_table2.ts_code = '" . $TsCode . "'
			// AND daily_table2.ts_code = astock_base.ts_code
   		    // AND trade_date = '" . $BuyTime . "';";
        $this->conn->query("SET NAMES 'utf8'");
        $result = $this->conn->query($sql);
        $result_arr = null;
        while ($row = $result->fetch_assoc()) {
            $result_arr = $row;
        }
        $result->free();
        return $result_arr;
    }
	

	/********************************************************************
	 * 功能：根据数据库中的信息，提取所有的股票交易时间
	 ********************************************************************/
	function select_all_buytime()
	{
		$sql =
		"SELECT DISTINCT(trade_date)
		FROM daily_table2
		ORDER BY trade_date;";
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row['trade_date']);
		}
		$result->free();
	}


	/********************************************************************
	 * 功能：根据数据库中的信息（根据表名，开始时间，结束时间），获取指数信息
	 ********************************************************************/
	function select_zhishu($tableName,$start,$end){
        $sql =
        "SELECT trade_date,close
		FROM ".$tableName."
		WHERE trade_date> '".$start."'
		AND trade_date< '".$end."';";
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row);
		}
		$result->free();
		return $result_arr;
    }

	/********************************************************************
	 * 功能：获取交易的时间
	 ********************************************************************/
	function getTradeTime()
	{
		$sql =
		"SELECT DISTINCT tradetime
		FROM tb_trade_time
		ORDER BY tradetime";
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row['tradetime']);
		}
		$result->free();
		return $result_arr;
	}

	/**
	 * 获取上证值数数据，计算基准线
	 */
	function get_avg_income(){
		$sql =
		"SELECT  trade_date , `close`
		FROM hushen300
		where trade_date>= '20150504'
		ORDER BY trade_date";
		$this->conn->query("SET NAMES 'utf8'");
		$result = $this->conn->query($sql);
		$result_arr = array();
		while ($row = $result->fetch_assoc()) {
			// 将从数据库获取的数据存放在数组内	
			array_push($result_arr, $row);
		}
		$result->free();
		return $result_arr;

	}


	/********************************************************************
	 * 功能：关闭数据库连接
	 ********************************************************************/
	function closeDatabase()
	{
		$this->conn->close();
	}
}

/** 建表临时
 *
 CREATE TABLE temp_daily2
(
ts_code varchar(100),
trade_date varchar(100),
open varchar(100),
close varchar(100),
updown_rate varchar(100)
)

// 导入数据库 
LOAD DATA LOCAL INFILE 'F:/Temp/all_in_one_nonefq.csv' 
INTO TABLE daily_table_copy_copy 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;



insert into tb_trade_time
SELECT DISTINCT(trade_date)
FROM daily_table2
ORDER BY trade_date
 *
 *
 */

/**  将结果放入临时表  
 *
    insert into temp_daily2(ts_code, trade_date, open,close,updown_rate) 
	SELECT daily_table2.ts_code, daily_table2.trade_date, open,close,updown_rate
	FROM daily_table2,
    astock_base,
	(SELECT DISTINCT ts_code,COUNT(ts_code)
	FROM daily_table2
	GROUP BY ts_code
	HAVING COUNT(ts_code)>100)
	as ttt
	WHERE ttt.ts_code = daily_table2.ts_code
	and astock_base.ts_code = daily_table2.ts_code
    AND astock_base.`name` NOT LIKE '%st%'
	AND astock_base.`name` NOT LIKE '%*st%'
	AND twentyday_ave>sixtyday_ave       
	AND sixtyday_ave>hundredtwenty_ave   
	AND (sixtyday_ave/hundredtwenty_ave)<1.04
	AND (twentyday_ave/hundredtwenty_ave)<1.1
	AND (`close`/hundredtwenty_ave)<1.1
	AND (`close`/hundredtwenty_ave)>1
	AND fiveday_ave>tenday_ave
	AND tenday_ave>twentyday_ave
	AND kdj_k<110
	AND kdj_d<110
	AND kdj_j<110
	AND (updown_rate > -9 AND updown_rate< 0)	
	AND fivedayhs < 8
	order by daily_table2.ts_code



	///修改 版本
	insert into temp_daily2
	(ts_code, trade_date, open,close,updown_rate,bkdm,
	polocy1_5,polocy1_10,polocy1_20,polocy1_30,polocy1_60,
	polocy2_5,polocy2_10,polocy2_20,polocy2_30,polocy2_60) 
	SELECT 
	daily_table2.ts_code, daily_table2.trade_date, open,close,updown_rate,bkdm,
	polocy1_5,polocy1_10,polocy1_20,polocy1_30,polocy1_60,
	polocy2_5,polocy2_10,polocy2_20,polocy2_30,polocy2_60
	FROM daily_table2,
	(SELECT DISTINCT ts_code,COUNT(ts_code)
	FROM daily_table2
	GROUP BY ts_code
	HAVING COUNT(ts_code)>100)
	as ttt
	WHERE ttt.ts_code = daily_table2.ts_code
    AND daily_table2.`name` NOT LIKE '%st%'
	AND daily_table2.`name` NOT LIKE '%*st%'
	AND twentyday_ave>sixtyday_ave
	AND sixtyday_ave>hundredtwenty_ave

	AND hundredtwenty_ave <>0
	AND (sixtyday_ave/hundredtwenty_ave)<1.04
	AND (twentyday_ave/hundredtwenty_ave)<1.1
	AND (`close`/hundredtwenty_ave)<1.1
	AND (`close`/hundredtwenty_ave)>1
	AND fiveday_ave>tenday_ave
	AND tenday_ave>twentyday_ave
	AND kdj_k<110
	AND kdj_d<110
	AND kdj_j<110
	AND (updown_rate > -9 AND updown_rate< 0)	
	AND fivedayhs < 8
	order by daily_table2.ts_code
 *
 */


"insert into temp_daily_2hour(ts_code, trade_date, open,close,updown_rate) 
 SELECT two_hour_daily.ts_code, two_hour_daily.trade_date, open,close,updown_rate
 FROM two_hour_daily,
    astock_base,
 (SELECT DISTINCT ts_code,COUNT(ts_code)
 FROM two_hour_daily
 GROUP BY ts_code
 HAVING COUNT(ts_code)>100)
 as ttt
 WHERE ttt.ts_code = two_hour_daily.ts_code
 and astock_base.ts_code = two_hour_daily.ts_code
    AND astock_base.`name` NOT LIKE '%st%'
 AND astock_base.`name` NOT LIKE '%*st%'
 AND twentyday_ave>sixtyday_ave
 AND sixtyday_ave>hundredtwenty_ave
 AND (sixtyday_ave/hundredtwenty_ave)<1.04
 AND (twentyday_ave/hundredtwenty_ave)<1.1
 AND (`close`/hundredtwenty_ave)<1.1
 AND (`close`/hundredtwenty_ave)>1
 AND fiveday_ave>tenday_ave
 AND tenday_ave>twentyday_ave
 AND kdj_k<110
 AND kdj_d<110
 AND kdj_j<110
 AND (updown_rate > -9 AND updown_rate< 0) 
 AND fivedayhs < 8
 order by two_hour_daily.ts_code";

?>